package com.ntd.study.database.study.datasource;

import cn.hutool.core.util.StrUtil;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 数据源代理类
 */
@Data
public class HandleDataSource {
    private DataSource dataSource;
    private Connection connection;

    /**
     * 处理数据来源
     *
     * @param jdbcUrl   jdbc url
     * @param className 类名
     * @param username  用户名
     * @param password  密码
     * @throws SQLException sqlexception异常
     */
    public HandleDataSource(String jdbcUrl, String className, String username, String password) throws SQLException {
        final HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(jdbcUrl);
        hikariConfig.setDriverClassName(className);
        hikariConfig.setUsername(username);
        hikariConfig.setPassword(password);
        dataSource = new HikariDataSource(hikariConfig);
        connection = dataSource.getConnection();
    }

    private Statement getStatement() throws SQLException {
        return connection.createStatement();
    }

    /**
     * 查询占用空间大小
     *
     * @param table_schema
     * @param table_name
     */
    public String getTableSize(String table_schema, String table_name) throws SQLException {
        String sql = "select concat(round(sum(DATA_LENGTH)/1024/1024,2),'M') as table_size" +
                " from information_schema.tables where 1 = 1";
        if (StrUtil.isNotBlank(table_schema)) {
            sql += String.format(" AND table_schema='%s'", table_schema);
        }
        if (StrUtil.isNotBlank(table_name)) {
            sql += String.format(" AND table_name='%s'", table_name);
        }
        final List<Map<String, Object>> maps = exeQuery(sql);
        return String.valueOf(maps.get(0).get("table_size"));
    }

    /**
     * 表空间优化
     *
     * @param table_schema
     * @param table_name
     */
    public void optimize(String table_schema, String table_name) throws SQLException {
        String sql = String.format("optimize table %s.%s", table_schema, table_name);
        exeQuery(sql);
    }

    /**
     * 获取数据 库列表
     *
     * @return
     * @throws SQLException
     */
    public List<String> getDatabases() throws SQLException {
        final List<Map<String, Object>> maps = exeQuery("show databases");
        return maps.stream().map(stringObjectMap -> String.valueOf(stringObjectMap.get("SCHEMA_NAME"))).collect(Collectors.toList());
    }

    /**
     * 获取数据 表列表
     *
     * @param databaseName
     * @return
     * @throws SQLException
     */
    public List<String> getTablesByDatabaseName(String databaseName) throws SQLException {
        final List<Map<String, Object>> maps = exeQuery(String.format("select * from information_schema.TABLES where TABLE_SCHEMA=('%s')", databaseName));
        return maps.stream().map(stringObjectMap -> String.valueOf(stringObjectMap.get("TABLE_NAME"))).collect(Collectors.toList());
    }

    /**
     * 获取 列数据
     *
     * @param databaseName
     * @param tableName
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> getColsByTableNameAndDatabaseName(String databaseName, String tableName) throws SQLException {
        return exeQuery(String.format("select * from information_schema.COLUMNS where TABLE_SCHEMA = ('%s') and TABLE_NAME='%s'", databaseName, tableName));
    }

    /**
     * 获取表数据
     *
     * @param databaseName
     * @param tableName
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> getTableDataByTableNameAndDatabaseName(String databaseName, String tableName) throws SQLException {
        return exeQuery(String.format("select * from %s.%s", databaseName, tableName));
    }

    /**
     * 查询语句执行
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> exeQuery(String sql) throws SQLException {
        final List<Map<String, Object>> maps = new ArrayList<>();
        final ResultSet rs = executeSql(sql);
        while (rs.next()) {
            Map<String, Object> map = new HashMap<>();
            final ResultSetMetaData metaData = rs.getMetaData();
            final int columnCount = metaData.getColumnCount();
            for (int i = 1; i < columnCount + 1; i++) {
                map.put(metaData.getColumnName(i), rs.getString(i));
            }
            maps.add(map);
        }
        return maps;
    }

    /**
     * 非查询语句执行
     *
     * @param sql
     * @return
     */
    public ResultSet executeSql(String sql) throws SQLException {
        final Statement statement = getStatement();
        try {
            return statement.executeQuery(sql);
        } finally {
            statement.close();
        }
    }
}
